package com.scalar.chiptrack.calendar.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.WorkWeek;
import com.scalar.chiptrack.calendar.forms.CalendarForm;
import com.scalar.chiptrack.calendar.Period;
import com.scalar.ScalarException;

import java.sql.*;
import java.util.Date;
import java.util.*;

/**
 * Created by Veeru. 
 * Date: Aug 3, 2004
 * Time: 7:23:35 PM 
 */
public class CalendarDAO {


    public CalendarDAO() {
    }
    public static void saveCalendar( DBTransaction dbTransaction, CalendarForm form,String[] quarterNo, String[] quarterName,String[] fromDate, String[] toDate )
            throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        CallableStatement cstmt = null;
        boolean success = false;
        int yearID = 0;

       try{

         // Get the connection from regular datasource if its not in a manual transaction
         if (dbTransaction == null) {
                conn = DataSource.getConnection();
         }
         else {
                conn = dbTransaction.getConnection();
         }
         cstmt = conn.prepareCall(SqlQueryMappings.get("calendar.saveCalendar"));
         if ( "-2".equals( form.getYear() ) ) {
            cstmt.setString(1, form.getNewYear());
         }
         else {
            cstmt.setString(1, form.getYear());
         }
         cstmt.setDate(2, StringUtils.getSQLDate(form.getFirstDay()));
         cstmt.setDate(3, StringUtils.getSQLDate(form.getLastDay()));
         cstmt.setInt(4, form.getNumberOfPeriods());
         cstmt.setInt(5,form.getJITWindow());
         cstmt.setDate(6, StringUtils.getSQLDate(form.getWwStartDate()));
         cstmt.registerOutParameter(7, java.sql.Types.BIGINT);
         cstmt.executeQuery();
         yearID  = cstmt.getInt(7);
         pstmt = conn.prepareStatement( SqlQueryMappings.get("calendar.insertQuarters"));
         for( int i = 0; i < fromDate.length; i++ )
         {
             pstmt.setInt( 1, yearID);
             pstmt.setString( 2, quarterName[i]);
             pstmt.setDate( 3, StringUtils.getSQLDate(fromDate[i]));
             pstmt.setDate( 4, StringUtils.getSQLDate(toDate[i]));
             pstmt.setString( 5, quarterNo[i]);

             pstmt.addBatch();
         }
         pstmt.executeBatch();
         pstmt.close();
         pstmt = null;
       }
       catch( Exception e){
            e.printStackTrace();
            throw new ScalarException("Could not save Calendar", e);
       }
       finally {
           if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }

            if(cstmt != null){
                try{ cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
       }
    }
    public static ArrayList loadYears() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList yearsList = new ArrayList();
        ResultSet rset = null;
        int year = 0;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadYears"));
            rset = pstmt.executeQuery();

            while (rset.next()) {
                year = rset.getInt("CC_Year");
                yearsList.add(new org.apache.struts.util.LabelValueBean(year+"",year+""));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Years from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return yearsList;
    }
    public static LinkedHashMap loadYearsMap() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        LinkedHashMap yearsMap = new LinkedHashMap();
        ResultSet rset = null;
        Period period = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadYears"));
            rset = pstmt.executeQuery();

            while (rset.next()) {
                period = new Period();

                period.setStartDate(StringUtils.formatDateToString( rset.getDate("CC_Start_Date"), null));
                period.setEndDate(StringUtils.formatDateToString( rset.getDate("CC_End_Date"), null));
                yearsMap.put((rset.getInt("CC_Year"))+"",period);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Years from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        return yearsMap;
    }
    public static void loadCalendar(CalendarForm form) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList periodList = new ArrayList();
        ResultSet rset = null;
        int yearID = 0;
        Period period = null;

        try {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadCompanyCalendar"));
            pstmt.setInt(1, Integer.parseInt(form.getYear()));
            rset = pstmt.executeQuery();
            while (rset.next()) {
                    yearID = rset.getInt("CC_YearID");
                    form.setYear(rset.getInt("CC_Year")+"");
                    form.setFirstDay(StringUtils.formatDateToString( rset.getDate("CC_Start_Date"), null));
                    form.setLastDay(StringUtils.formatDateToString( rset.getDate("CC_End_Date"), null));
                    form.setNumberOfPeriods(rset.getInt("CC_NO_Of_Periods"));
                    form.setWwStartDate( StringUtils.formatDateToString( rset.getDate("WW_START_DATE"), null) );
            }

            rset.close();
            pstmt.close();
            pstmt = null;
            pstmt = conn.prepareStatement(SqlQueryMappings.get("calendar.loadQuarters"));
            pstmt.setInt(1,yearID);
            rset = pstmt.executeQuery();
            while(rset.next()) {
                    period = new Period();
                    period.setQuarter(rset.getString("CQC_Quarter"));
                    period.setStartDate(StringUtils.formatDateToString( rset.getDate("CQC_Start_Date"), null) );
                    period.setEndDate(StringUtils.formatDateToString( rset.getDate("CQC_End_Date"), null) );
                    periodList.add(period);
            }
            form.setPeriodList(periodList);
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException("Could not load Years from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    public static void calculateFiscalPeriods (  CalendarForm form ) throws ScalarException
    {
        try{
                ArrayList periodlist = new ArrayList();
                int numberofperiods = form.getNumberOfPeriods();
                String DATE_FORMAT = "MM/dd/yyyy";
                java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(DATE_FORMAT);

                java.util.Date firstday = StringUtils.format( form.getFirstDay() );
                java.util.Date lastday  = StringUtils.format( form.getLastDay() );
                int days = (int)((lastday.getTime() - firstday.getTime()) / (1000*60*60*24));
                days++;
                Calendar firstdayCalendar = Calendar.getInstance();
                Calendar lastdayCalendar = Calendar.getInstance();
                firstdayCalendar.setTime( firstday );
                lastdayCalendar.setTime( lastday );
                if ( ( days == 365 || days == 366 )  &&  ( 12 % numberofperiods )  == 0  )
                {
                     for( int i = 1; i <= numberofperiods ; i++)
                     {
                          Period period = new Period();
                          period.setQuarter("Quarter "+ i  );
                          period.setStartDate( sdf.format(firstdayCalendar.getTime()) );
                          firstdayCalendar.add(Calendar.MONTH, (12 / numberofperiods ));
                          firstdayCalendar.add (Calendar.DATE,-1);
                          if( i == numberofperiods )
                          {
                              period.setEndDate(sdf.format(lastdayCalendar.getTime()));
                          }
                          else
                              period.setEndDate(sdf.format(firstdayCalendar.getTime()));
                          firstdayCalendar.add (Calendar.DATE, +1);
                          periodlist.add(period);
                     }
                }
                else
                {
                 if( numberofperiods <= days  )
                 {
                    for( int i=1; i<= numberofperiods ; i++)
                    {
                         Period period = new Period();
                         period.setQuarter("Quarter "+ i  );
                         period.setStartDate( sdf.format(firstdayCalendar.getTime()) );
                         firstdayCalendar.add (Calendar.DATE, days/numberofperiods-1 );
                         if( i == numberofperiods )
                          {
                              period.setEndDate(sdf.format(lastdayCalendar.getTime()));
                          }
                          else
                              period.setEndDate(sdf.format(firstdayCalendar.getTime()));
                         firstdayCalendar.add (Calendar.DATE, +1);
                         periodlist.add(period);
                    }
                 }
                 else
                 {
                    int i;
                    numberofperiods--;
                    for(  i=1; i<= numberofperiods ; i++)
                    {
                         Period period = new Period();
                         period.setQuarter("Quarter "+ i  );
                         period.setStartDate( sdf.format(firstdayCalendar.getTime()) );
                         period.setEndDate( sdf.format(firstdayCalendar.getTime()) );
                         firstdayCalendar.add (Calendar.DATE, days/numberofperiods );
                         periodlist.add(period);
                    }
                     Period period = new Period();
                     period.setQuarter("Quarter "+ i  );
                     period.setStartDate( sdf.format(firstdayCalendar.getTime()) );
                     period.setEndDate(sdf.format(lastdayCalendar.getTime()));
                     periodlist.add(period);
                 }
                }
                form.setPeriodList( periodlist );
                }
                catch ( Throwable th )
                {
                    throw new ScalarException( "Could not calculateFiscalPeriods", th );
                }

            }
    /**/

    public static int getJitWindow( DBTransaction dbTransaction, int year) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int jitWindow = -1;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( SqlQueryMappings.get("load.companyCalendarJITWindow") );
            pstmt.setInt( 1, year );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                jitWindow = rset.getInt( "CC_JIT_WINDOW" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive jitWindow", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return jitWindow;
    }
    public static String getCompanyClaendarWorkWeek( DBTransaction dbTransaction, String dateToFindWW) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        String workWeekNo = null;


        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "SELECT WW_NUMBER FROM FN_CALCULATE_WORK_WEEK (?)" );
            pstmt.setString( 1, dateToFindWW );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
                workWeekNo = rset.getInt( "WW_NUMBER" ) >= 10 ? rset.getInt( "WW_NUMBER" )+"" : "0" + rset.getInt( "WW_NUMBER" );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive work week no based on Company Calendar", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return workWeekNo;
    }
    public static String getWorkWeek( String date )
    {
        String result      = null;
        /*Calendar calendar = Calendar.getInstance();

        int year  = 0;
        int month = 0;
        int day   = 1;

		int daysSkipped    = 0;
		int daysUntilToday = 0;
        int workWeek       = 0;


        if ( date == null || date.trim().length() == 0  )
        {
            year  = calendar.get( Calendar.YEAR ) ;
        }
        else
        {
            String array[] = date.split("/");
            year  = Integer.parseInt( array[2] );
            month = Integer.parseInt( array[0] );
            day   = Integer.parseInt( array[1] );
        }
		calendar.set( year, 0, 1 ) ;
	    String week = calendar.getTime().toString().substring( 0, 3);

		if ( "Fri".equalsIgnoreCase( week ) )
		{
			daysSkipped = 3;
		}
		if ( "Sat".equalsIgnoreCase( week ) )
		{
			daysSkipped = 2;
		}
		if ( "Sun".equalsIgnoreCase( week ) )
		{
			daysSkipped = 1;
		}

		for ( int j = 0; j < month - 1; j++ ) { //count days in all previous months
		  daysUntilToday += getDays( j, year );
		}
		daysUntilToday += day;
        workWeek = ( daysUntilToday - daysSkipped ) / 7;
        if ( workWeek > 0 && ( daysUntilToday - daysSkipped ) % 7 > 0 )
        {
            workWeek += 1;
        }
        if ( workWeek <= 1 )
        {
            result = "Work Week "+1+" starting on date "+ 01+"/"+( daysSkipped + 1 )+ "/" +year;
        }
        else
        {
            result = "Entered date belongs to the Work Week "+workWeek;
        }*/
        WorkWeek workWeek = WorkWeek.getWorkWeekInstance( StringUtils.format( date ) );
        result = "Entered date belongs to the Work Week '"+ workWeek.getCurrentWWNumber() + "' <br> Work Week Starts From: '" + StringUtils.formatDateToString( workWeek.getCurrentWWStartDate() ) + "'";

        return result;
    }

	public static  int getDays(int month, int year) {
		// create array to hold number of days in each month
		int ar[] = new int[12];
		ar[0] = 31; // January
		ar[1] = (year % 4 == 0) ? 29 : 28; // February
		ar[2] = 31; // March
		ar[3] = 30; // April
		ar[4] = 31; // May
		ar[5] = 30; // June
		ar[6] = 31; // July
		ar[7] = 31; // August
		ar[8] = 30; // September
		ar[9] = 31; // October
		ar[10] = 30; // November
		ar[11] = 31; // December

		// return number of days in the specified month (parameter)
		return ar[month];
	}
	
    public static Period getQuarterByCompanyCalender( Date date ) throws ScalarException
    {
    	Period period = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT CONVERT(VARCHAR, CQC_START_DATE, 101) as CQC_START_DATE, CONVERT(VARCHAR,CQC_END_DATE, 101) AS CQC_END_DATE, CQC_QUARTER_NO, CQC_QUARTER FROM Company_Quarterly_Calendar WHERE CQC_START_DATE <= ? AND CQC_END_DATE >= ?" );
            Calendar cal = Calendar.getInstance();
            cal.setTimeInMillis( date.getTime() );
            System.out.println( cal.get( Calendar.MONTH ) );
            System.out.println( new java.sql.Date( date.getTime() ) );
            pstmt.setDate( 1, new java.sql.Date( date.getTime() ) );
            pstmt.setDate( 2, new java.sql.Date( date.getTime() ) );
            rset = pstmt.executeQuery();
            if( rset.next() )
            {
            	period = new Period();
            	period.setStartDate( rset.getString( "CQC_START_DATE" ) );
            	period.setEndDate( rset.getString( "CQC_END_DATE" ) );
            	period.setQuarterId( rset.getInt( "CQC_QUARTER_NO" ) );
            	period.setQuarter( rset.getString( "CQC_QUARTER" ) );
            }

        }
        catch (Exception sqle)
        {
            sqle.printStackTrace();
            throw new ScalarException ("could not retrive quarter info based on Company Calendar", sqle);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if ( pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
		return period;
    }
}
